﻿Imports System.Data.SqlClient
Public Class frmRCapacitaciones
    Dim mdsDatos As New DataSet
    Dim ExistenRegistros As Boolean
    Private Sub frmRCapacitaciones_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ToolStrip1.Cursor = Cursors.Hand
        ToolStrip1.RenderMode = System.Windows.Forms.ToolStripRenderMode.System

        CargaDeptos()
        cargaPersonal()
        radioTodos.Checked = True
        ExistenRegistros = False
    End Sub
    Sub cargaPersonal()
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection

        Dim cmdBodega As New SqlCommand

        cmdBodega.Connection = cnconn
        cmdBodega.CommandText = "Select IdEmpleado, NombreCompleto " & _
                                    " From Empleados " & _
                                    " Order By NombreCompleto "
        Dim daDesarrollo As New SqlDataAdapter
        daDesarrollo.SelectCommand = cmdBodega

        If mdsDatos.Tables.Contains("Empleados") Then
            mdsDatos.Tables("Empleados").Rows.Clear()
        End If

        daDesarrollo.Fill(mdsDatos, "Empleados")

        lstNombre.DataSource = mdsDatos.Tables("Empleados").DefaultView
        lstNombre.DisplayMember = "NombreCompleto"
        lstNombre.ValueMember = "IdEmpleado"
    End Sub
    Sub CargaDeptos()
        Dim cnconn As New SqlConnection
        cnconn.ConnectionString = CitraConnection

        Dim cmdBodega As New SqlCommand

        cmdBodega.Connection = cnconn
        cmdBodega.CommandText = "Select IdDepartamento, Descripcion " & _
                                    " From Departamentos " & _
                                    " Order By Descripcion "
        Dim daDesarrollo As New SqlDataAdapter
        daDesarrollo.SelectCommand = cmdBodega

        If mdsDatos.Tables.Contains("Departamentos") Then
            mdsDatos.Tables("Departamentos").Rows.Clear()
        End If

        daDesarrollo.Fill(mdsDatos, "Departamentos")

        lstDepartamento.DataSource = mdsDatos.Tables("Departamentos").DefaultView
        lstDepartamento.DisplayMember = "Descripcion"
        lstDepartamento.ValueMember = "IdDepartamento"


    End Sub
    Private Sub radioTodos_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radioTodos.CheckedChanged
        If radioTodos.Checked = True Then
            Me.lstDepartamento.Enabled = False
            Me.lstNombre.Enabled = False
        End If
    End Sub

    Private Sub radioNombre_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radioNombre.CheckedChanged
        If radioNombre.Checked = True Then
            Me.lstNombre.Enabled = True
        Else
            Me.lstNombre.Enabled = False
        End If
    End Sub

    Private Sub radioDepto_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles radioDepto.CheckedChanged
        If radioDepto.Checked = True Then
            Me.lstDepartamento.Enabled = True
        Else
            Me.lstDepartamento.Enabled = False
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)


    End Sub
    Sub todos()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6
        ExistenRegistros = False

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15
        'Primero Obtener todos los nombres de los empleados

        Dim strSql As String = ""
        Dim cont As Integer = 0
        Dim i As Integer = 0

        'Segundo, obtener los registros de gratificaciones
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        strSql = "select T.idempleado,tarjeton,tipo,modulo,FechaExpedicion,fechaexpira,status, NombreCompleto"
        strSql = strSql & " from Capacitacion T INNER JOIN Empleados E ON t.idempleado=e.idempleado Where EstadoEmpleado<>'B'"
        strSql = strSql & " order by t.idempleado, fechaexpira desc"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim strFechaMov As String = ""
        Dim Empleado As String = "?"
        Dim IDActual As Integer

        Do While rdBuscar.Read()
            If IDActual <> rdBuscar("idempleado") Then
                If Empleado <> "?" Then
                    rng = rng + 1
                End If
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 1).value = "Nombre: " & rdBuscar("NombreCompleto")
                wb.SHEETS(1).cells(rng, 1).font.size = 12
                wb.SHEETS(1).cells(rng, 1).font.bold = -1
                rng = rng + 1

                wb.SHEETS(1).cells(rng, 1).value = "Tarjeton"
                wb.SHEETS(1).cells(rng, 2).value = "Tipo"
                wb.SHEETS(1).cells(rng, 3).value = "Modulo"
                wb.SHEETS(1).cells(rng, 4).value = "Expedido"
                wb.SHEETS(1).cells(rng, 5).value = "Expira"
                wb.SHEETS(1).cells(rng, 6).value = "Status"
                For i = 1 To 6
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                    wb.SHEETS(1).cells(rng, i).font.bold = True
                Next
                rng = rng + 1
            End If
            ExistenRegistros = True
            cont = cont + 1
            IDActual = rdBuscar("idempleado")
            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("tarjeton")
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("tipo")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("modulo")
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("FechaExpedicion")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("fechaexpira")
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("status")


            For i = 1 To 6
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1

        Loop

        If ExistenRegistros = False Then
            wb.SHEETS(1).cells(rng, 1).value = "No se encontrarón registros para mostrar"
        End If

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 10


        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Capacitaciones. Filtro: Todos"
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub
    Sub listado()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6
        ExistenRegistros = False

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15
        'Primero Obtener todos los nombres de los empleados

        Dim strSql As String = ""
        Dim cont As Integer = 0
        Dim i As Integer = 0

        'Segundo, obtener los registros de gratificaciones
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        strSql = "select T.idempleado,tarjeton,tipo,modulo,FechaExpedicion,fechaexpira,status, NombreCompleto"
        strSql = strSql & " from Capacitacion T INNER JOIN Empleados E ON t.idempleado=e.idempleado Where EstadoEmpleado<>'B'"
        strSql = strSql & " order by t.idempleado, fechaexpira desc"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim Empleado As String = "?"
        wb.SHEETS(1).cells(rng, 1).value = "Nombre"
        wb.SHEETS(1).cells(rng, 2).value = "Tarjeton"
        wb.SHEETS(1).cells(rng, 3).value = "Tipo"
        wb.SHEETS(1).cells(rng, 4).value = "Modulo"
        wb.SHEETS(1).cells(rng, 5).value = "Expedido"
        wb.SHEETS(1).cells(rng, 6).value = "Expira"
        wb.SHEETS(1).cells(rng, 7).value = "Status"
        For i = 1 To 8
            wb.SHEETS(1).cells(rng, i).font.size = 9
            wb.SHEETS(1).cells(rng, i).font.bold = True
        Next
        rng = rng + 1
        Do While rdBuscar.Read()
            cont = cont + 1
            ExistenRegistros = True
            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("nombrecompleto")
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("tarjeton")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("tipo")
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("modulo")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("FechaExpedicion")
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("fechaexpira")
            wb.SHEETS(1).cells(rng, 7).value = rdBuscar("status")
            For i = 1 To 8
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1

        Loop

        If ExistenRegistros = False Then
            wb.SHEETS(1).cells(rng, 1).value = "No se encontrarón registros para mostrar"
        End If
        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 25
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(7).ColumnWidth = 10

        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Uniformes. Filtro: Listado"
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8

    End Sub
    Sub departamentos()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6
        ExistenRegistros = False

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15
        'Primero Obtener todos los nombres de los empleados

        Dim strSql As String = ""
        Dim cont As Integer = 0
        Dim i As Integer = 0

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        strSql = "select T.idempleado,tarjeton,tipo,modulo,FechaExpedicion,fechaexpira,status, NombreCompleto"
        strSql = strSql & " from Capacitacion T INNER JOIN Empleados E ON t.idempleado=e.idempleado INNER JOIN Departamentos D ON E.IdDepartamento=D.IdDepartamento Where EstadoEmpleado<>'B' and Descripcion='" & Me.lstDepartamento.Text.Trim & "'"
        strSql = strSql & " order by t.idempleado, fechaexpira desc"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim Empleado As String = "?"
        Dim IDActual As Integer

        Do While rdBuscar.Read()
            If IDActual <> rdBuscar("idempleado") Then
                If Empleado <> "?" Then
                    rng = rng + 1
                End If
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 1).value = "Nombre: " & rdBuscar("NombreCompleto")
                wb.SHEETS(1).cells(rng, 1).font.size = 12
                wb.SHEETS(1).cells(rng, 1).font.bold = -1
                rng = rng + 1

                wb.SHEETS(1).cells(rng, 1).value = "Tarjeton"
                wb.SHEETS(1).cells(rng, 2).value = "Tipo"
                wb.SHEETS(1).cells(rng, 3).value = "Modulo"
                wb.SHEETS(1).cells(rng, 4).value = "Expedido"
                wb.SHEETS(1).cells(rng, 5).value = "Expira"
                wb.SHEETS(1).cells(rng, 6).value = "Status"
                For i = 1 To 6
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                    wb.SHEETS(1).cells(rng, i).font.bold = True
                Next
                rng = rng + 1
            End If
            ExistenRegistros = True
            cont = cont + 1
            IDActual = rdBuscar("idempleado")
            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("tarjeton")
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("tipo")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("modulo")
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("FechaExpedicion")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("fechaexpira")
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("status")

            For i = 1 To 6
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1
        Loop

        If ExistenRegistros = False Then
            wb.SHEETS(1).cells(rng, 1).value = "No se encontrarón registros para mostrar"
        End If

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 10


        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Uniformes. Filtro: Departamento: " & Me.lstDepartamento.Text.Trim
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8

    End Sub
    Sub empleados()
        Dim xl As Object
        Dim wb As Object
        xl = CreateObject("Excel.Application")
        xl.Visible = True
        xl.Workbooks.Add()
        wb = xl.activeworkbook
        Dim rng As Integer
        rng = 6
        ExistenRegistros = False

        wb.SHEETS(1).cells(1, 1).value = "Imprimiendo..."
        wb.SHEETS(1).columns(1).columnwidth = 15

        Dim strSql As String = ""

        Dim cont As Integer = 0
        Dim i As Integer = 0

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        strSql = "select T.idempleado,tarjeton,tipo,modulo,FechaExpedicion,fechaexpira,status, NombreCompleto"
        strSql = strSql & " from Capacitacion T INNER JOIN Empleados E ON t.idempleado=e.idempleado Where EstadoEmpleado<>'B' and NombreCompleto='" & Me.lstNombre.Text.Trim & "'"
        strSql = strSql & " order by t.idempleado, fechaexpira desc"


        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = strSql
        Dim rdBuscar As SqlDataReader
        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim Empleado As String = "?"
        Dim IDActual As Integer
        Do While rdBuscar.Read()
            If IDActual <> rdBuscar("idempleado") Then
                If Empleado <> "?" Then
                    rng = rng + 1
                End If
                rng = rng + 1
                wb.SHEETS(1).cells(rng, 1).value = "Nombre: " & Me.lstNombre.Text.Trim
                wb.SHEETS(1).cells(rng, 1).font.size = 12
                wb.SHEETS(1).cells(rng, 1).font.bold = -1
                rng = rng + 1

                wb.SHEETS(1).cells(rng, 1).value = "Tarjeton"
                wb.SHEETS(1).cells(rng, 2).value = "Tipo"
                wb.SHEETS(1).cells(rng, 3).value = "Modulo"
                wb.SHEETS(1).cells(rng, 4).value = "Expedido"
                wb.SHEETS(1).cells(rng, 5).value = "Expira"
                wb.SHEETS(1).cells(rng, 6).value = "Status"
                For i = 1 To 6
                    wb.SHEETS(1).cells(rng, i).font.size = 9
                    wb.SHEETS(1).cells(rng, i).font.bold = True
                Next
                rng = rng + 1
            End If
            ExistenRegistros = True
            cont = cont + 1
            IDActual = rdBuscar("idempleado")
            wb.SHEETS(1).cells(rng, 1).value = rdBuscar("tarjeton")
            wb.SHEETS(1).cells(rng, 2).value = rdBuscar("tipo")
            wb.SHEETS(1).cells(rng, 3).value = rdBuscar("modulo")
            wb.SHEETS(1).cells(rng, 4).value = rdBuscar("FechaExpedicion")
            wb.SHEETS(1).cells(rng, 5).value = rdBuscar("fechaexpira")
            wb.SHEETS(1).cells(rng, 6).value = rdBuscar("status")


            For i = 1 To 6
                wb.SHEETS(1).cells(rng, i).font.size = 9
            Next
            rng = rng + 1

        Loop

        If ExistenRegistros = False Then
            wb.SHEETS(1).cells(rng, 1).value = "No se encontrarón registros para mostrar"
        End If

        wb.SHEETS(1).COLUMNS(1).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(2).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(3).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(4).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(5).ColumnWidth = 10
        wb.SHEETS(1).COLUMNS(6).ColumnWidth = 10


        wb.SHEETS(1).cells(1, 1).value = EmpresaActiva
        wb.SHEETS(1).cells(1, 1).FONT.SIZE = 14
        wb.SHEETS(1).cells(2, 1).value = "Informe de Capacitaciones. Filtro: Empleado: " & Me.lstNombre.Text.Trim
        wb.SHEETS(1).cells(2, 1).FONT.SIZE = 10
        wb.SHEETS(1).cells(3, 1).value = "Impreso por: " & usuarioactivo & " el " & Now().Day & "-" & Now().Month & "-" & Now().Year
        wb.SHEETS(1).cells(3, 1).FONT.SIZE = 8
        wb.SHEETS(1).cells(4, 1).value = "Registros del filtro: " & cont
        wb.SHEETS(1).cells(4, 1).FONT.SIZE = 8
    End Sub

    Private Sub RadButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Private Sub btnAbrir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

    End Sub

    Private Sub ToolStripButton2_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton2.Click
        If radioTodos.Checked = True Then
            todos()
        ElseIf radioDepto.Checked = True Then
            departamentos()
        ElseIf radioNombre.Checked = True Then
            empleados()
        Else
            listado()

        End If
    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Close()

    End Sub
End Class